package com.situ.staff.employee.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
//import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.situ.staff.employee.model.EmployeeModel;
import com.situ.staff.util.FmtEmpty;
import com.situ.staff.util.JDBCUtil;

public class EmployeeDao {

	/**
	 * 添加
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public Integer insert(EmployeeModel model) throws SQLException {
		String sql = "insert into employee (code,name,password,code_dept) values(?,?,?,?)";
		return JDBCUtil.update(sql, model.getCode(), model.getName(), model.getPasswrod(), model.getCodeDept());
	}

	/**
	 * 通过号码删除
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public Integer deleteByCode(EmployeeModel model) throws SQLException {
		String sql = "delete from employee where code =?";
		return JDBCUtil.update(sql.toString(), model.getCode());
	}

	/**
	 * 删除
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public Integer delete(EmployeeModel model) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from employee");
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 更新所有字段
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public Integer updateAll(EmployeeModel model) throws SQLException {
		String sql = "update employee set name = ? , password = ?,code_dept=? where code =?";
		return JDBCUtil.update(sql, model.getName(), model.getPasswrod(), model.getCodeDept(), model.getCode());
	}

	/**
	 * 更新部分字段
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public Integer updateActive(EmployeeModel model) throws SQLException {
//		String sql = "update employee set name = ? and tel = ? where code =?";
		StringBuffer sql = new StringBuffer("update employee set id=id ");
		List<Object> vals = new ArrayList<Object>();
		String name = model.getName();
		if (name != null) {
			sql.append(" ,name=?");
			vals.add(name);
		}
		String password = model.getPasswrod();
		if (password != null) {
			sql.append(" ,password=?");
			vals.add(password);
		}
		String codeDept = model.getCodeDept();
		if (codeDept != null) {
			sql.append(" ,code_dept=?");
			vals.add(codeDept);
		}
		String image = model.getImage();
		if (image != null) {
			sql.append(" ,image=?");
			vals.add(image);
		}
		sql.append(" where code = ?");
		vals.add(model.getCode());
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 查询所有
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<EmployeeModel> selectList(EmployeeModel model) throws SQLException {
		StringBuffer sql = new StringBuffer(
				"select code,name,password,code_dept,image,(select name from department where code=employee.code_dept) deptName from employee ");
		List<Object> vals = appendWhere(sql, model);
//		List<String> props = Arrays.asList("code", "name", "password","codeDept","deptName");
		return JDBCUtil.query(sql.toString(), vals, EmployeeModel.class, getFields());
	}

	/**
	 * 条件拼接和sql拼接
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendWhere(StringBuffer sql, EmployeeModel model) {
		// TODO Auto-generated method stub
		List<Object> vals = new ArrayList<Object>();
		sql.append(" where 1=1 ");
		String code = model.getCode();
		if (!FmtEmpty.isEmpty(code)) {
			vals.add(code);
			sql.append(" and code like ?");
		}
		String name = model.getName();
		if (!FmtEmpty.isEmpty(name)) {
			vals.add(name);
			sql.append(" and name like ?");
		}
		String password = model.getPasswrod();
		if (!FmtEmpty.isEmpty(password)) {
			vals.add(password);
			sql.append(" and password like ?");
		}
		String codeDept = model.getCodeDept();
		if (!FmtEmpty.isEmpty(codeDept)) {
			vals.add(codeDept);
			sql.append(" and code_dept like ?");
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,?");
			// 第一页 0,10
			// 第二页 10,10
			// 第三页 20,10
			vals.add(model.getRowStart());
			vals.add(model.getPageLimit());
		}
		return vals;
	}

	/**
	 * 查询个体
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public EmployeeModel selectModel(EmployeeModel model) throws SQLException {
		StringBuffer sql = new StringBuffer(
				"select code,name,password,code_dept,image,(select name from department where code=employee.code_dept) deptName from employee");
		List<Object> vals = appendWhere(sql, model);
		List<String> props = Arrays.asList("code", "name", "password", "image", "codeDept");
		return JDBCUtil.queryModel(sql.toString(), vals, EmployeeModel.class, props);
	}

	private Map<String, String> getFields() {
		Map<String, String> map = new HashMap<String, String>();
		map.put("code", "code");
		map.put("name", "name");
		map.put("password", "password");
		map.put("codeDept", "code_dept");
		map.put("image", "image");
		map.put("deptName", "deptName");
		return map;
	}

	public Integer selectCount(EmployeeModel m0) {
		StringBuffer sql = new StringBuffer("select count(*) from employee");
		List<Object> vals = appendWhere(sql, m0);

		return JDBCUtil.queryInt(sql.toString(), vals);
	}
}
